-- =============================================
-- Procedure: CreateUser
-- Purpose:	Creates a user
-- Author: Dale McFarlane
-- Created: 2013-1-20
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  
-- =====================================================================================

-- EXEC 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CreateUser]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[CreateUser]
END
GO

CREATE PROCEDURE CreateUser
@username varchar(50),
@first_name varchar(100),
@last_name varchar(100),
@email_address varchar(255),
@password varchar(255),
@created_date datetime,
@role_id bigint

AS
BEGIN
	DECLARE @error VARCHAR(100)
	DECLARE @error_message VARCHAR(MAX)
	DECLARE @user_id BIGINT
	
	BEGIN TRANSACTION
	
	BEGIN TRY	
				
		IF NOT EXISTS(SELECT * FROM dbo.[user] WHERE username = @username)
		BEGIN
			IF EXISTS(SELECT * FROM dbo.[role] WHERE role_id = @role_id)
			BEGIN
				INSERT INTO dbo.[user](
					username,
					first_name,
					last_name,
					email_address,
					password,
					created_date
				)
				VALUES(
					@username,
					@first_name,
					@last_name,
					@email_address,
					@password,
					GETDATE()
				)
				
				SET @user_id = SCOPE_IDENTITY()
				
				INSERT INTO dbo.[user_role](
					user_id,
					role_id,
					created_date
				)
				VALUES(
					@user_id,
					@role_id,
					GETDATE()					
				)
			END
			ELSE
			BEGIN
				SET @error = 'ERR0002'
				SET @error_message = 'The selected role does not exist'
			END			
		END	
		ELSE
		BEGIN
			SET @error = 'ERR0001'
			SET @error_message = 'User already exist'
		END
		
	END TRY
	BEGIN CATCH
		SET @error = @@ERROR
		SET @error_message = ERROR_MESSAGE()
	END CATCH
	
	
	SELECT 
		@user_id AS 'identity',
		@error AS 'error_code',
		@error_message AS 'error_message'	
	
	IF @error IS NOT NULL 
	BEGIN
		ROLLBACK TRANSACTION;
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION;
	END
END
GO